﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading;
using System.Data.SQLite;
using System.Data.Common;
using System.Data;
using System.Xml;
using ImapX;

namespace ExportFethionHistory
{
    class ExportFethionHistory
    {
        Form1 parentUI;
        private Thread thread;
        static String chatlogTag = "Fethion";
        static String gmailImap = "imap.gmail.com";
        ImapX.FolderCollection folders;
        ImapX.ImapClient client;
        String user;
        String pass;
        String filepath;
        String fId;
        bool boolTest;

        public ExportFethionHistory(Form1 form, bool test)
        {
            this.parentUI = form;
            this.boolTest = test;
        }

        public int StartExport(String user, String pass, String filepath, String fId)
        {
            this.user = user;
            this.pass = pass;
            this.filepath = filepath;
            this.fId = fId;
            thread = new Thread(new ThreadStart(Run));
            thread.Start();

            return 0;
        }

        private void Run()
        {
            parentUI.SetEnable(false);
            if (InitConn(user, pass))
                ExportAndSaveHistory(filepath, fId);
            parentUI.SetEnable(true);
        }
        public bool InitConn(String user, String pass)
        {
                client = new ImapX.ImapClient(gmailImap, 993, true);
                bool result = false;

                result = client.Connection();
                client.IsDebug = false;
                if (result)
                {
                    parentUI.AddLog("IMAP server connected");
                }
                else
                {
                    parentUI.AddLog("Can not connect to gmail");
                    return false;
                }

                result = client.LogIn(user, pass);
                if (result)
                {
                    parentUI.AddLog("Login success");
                }
                else
                {
                    parentUI.AddLog("Login failed, check username/password");
                    return false;
                }

                //Folder Collection
                //Create folder
                client.CreateFolder(chatlogTag);
                folders = client.Folders;

                return true;
        }

        string GetBase64String(string str)
        {
            return "=?UTF8?B?" + Convert.ToBase64String(Encoding.UTF8.GetBytes(str)) + "?=";

        }

        bool SaveRecord(ChatlogItem[] chatlogs,String myID)
        {
            String sub = chatlogs[0].subject;

            // To find if there's been a conversation with the same person
            /*String msgRef = "";
                        ImapX.MessageCollection messages = client.Folders[chatlogTag].Search("SUBJECT " + sub , true);
                        if (messages.Count > 0)
                        {
                            msgRef = messages[0].MessageId;
                            parentUI.AddLog("All log refer to MsgID: "+msgRef);
                        }
            */
            if (boolTest)
                return false;
            ImapX.Message msg = new ImapX.Message();
            msg.Subject = GetBase64String(chatlogs[0].subject);
            msg.ContentType = "text/html";

            String fromUserName = GetBase64String(chatlogs[0].fromName);
            String fromUserEmail = chatlogs[0].fromID+ "@Fethion.com";
//            String toUserName =  GetBase64String(chatlogs[0].toName);;
//            String toUserEmail = chatlogs[0].toID+"@Fethion.com";
            if (myID == chatlogs[0].fromID)
            {
                fromUserName = GetBase64String(chatlogs[0].toName);
                fromUserEmail = chatlogs[0].toID + "@Fethion.com";
            }
            msg.From.Add(new ImapX.MailAddress(fromUserName, fromUserEmail));
//            msg.To.Add(new ImapX.MailAddress(toUserName, toUserEmail));
            msg.HtmlBody.ContentType = "text/html";

            for (int i = 0; i < chatlogs.Length; i++)
            {
                msg.HtmlBody.TextData += chatlogs[i].content + "<br />";
            }

            msg.Date = chatlogs[0].date;

            if (client.Folders[chatlogTag].AppendMessage(msg, chatlogTag))
                return true;
            return false;
        }


        String[] GetDistinctNames(SQLiteConnection cnn,String myID)
        {
            string sqlDistinctName = @"SELECT distinct SenderName from MessageBody where MessageBody.[SenderUri]!='"+myID+"';";
            SQLiteCommand myCommand = new SQLiteCommand(sqlDistinctName, cnn);
            int ret = myCommand.ExecuteNonQuery();

            SQLiteDataReader reader = myCommand.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Load(reader);

            DataRow dr = dt.Rows[0];
            // String xmlStr=GetXml(dt);

            String[] distinctNames = new String[dt.Rows.Count];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow row = dt.Rows[i];
                distinctNames[i] = row[0].ToString();
            }
            return distinctNames;
        }

        public void ExportAndSaveHistory(String filepath, String fId)
        {
            string connStr = @"data source=" + filepath + ";Password=" + fId;

            // Query for add record column
            string sqlColumn = @"ALTER TABLE MessageBody ADD record integer default 0";

            // Connection code:
            SQLiteConnection cnn = null;
            SQLiteCommand myCommand;
            int ret;

            /* Create a new column named record
             * Once the record has been send to gmail 
             * this column will be set 1 or default 0
             */
            try
            {
                cnn = new SQLiteConnection(connStr);
                cnn.Open();
            }
            catch (Exception e)
            {
                parentUI.AddLog("History file or Fethion ID error");
                return;
            }
            try
            {
                myCommand = new SQLiteCommand(sqlColumn, cnn);
                ret = myCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                parentUI.AddLog("Record column existed");
            }

            try
            {
                String[] distinctNames = GetDistinctNames(cnn,fId);

                // Search for all chat log with everyone else
                for (int i = 0; i < distinctNames.Length; i++)
                {
                    parentUI.SetControlPropertyValue((int)(i * 100 / distinctNames.Length + 1));
                    string sqlSingleName = @"SELECT MessageBody.ID AS ID, MessageBody.SenderUri AS SenderUri , MessageBody.SenderName AS SenderName , Message.ReceiverUri AS ReceiverUri , Message.ReceiverName AS ReceiverName , MessageBody.Body AS Body , MessageBody.[Time] AS [Time], MessageBody.[record] AS [record]
                        FROM Message INNER JOIN 
                        MessageBody ON Message.MessageID = MessageBody.ID 
                        INNER JOIN Player P1 ON Message.ReceiverUri = P1.Uri 
                        INNER JOIN Player P2 ON MessageBody.SenderUri = P2.Uri
                        where MessageBody.[record]=0 and ( MessageBody.[SenderName]='" + distinctNames[i] + "' or Message.[ReceiverName]='" + distinctNames[i] + "') order by Time ";

                    myCommand = new SQLiteCommand(sqlSingleName, cnn);
                    ret = myCommand.ExecuteNonQuery();

                    SQLiteDataReader reader = myCommand.ExecuteReader();
                    DataTable dt = new DataTable();
                    dt.Load(reader);

                    int j = 0;
                    ChatlogItem[] chatlogs = new ChatlogItem[dt.Rows.Count];
                    parentUI.AddLog("There are " + dt.Rows.Count + " chat messages with " + distinctNames[i]);
                    foreach (DataRow row in dt.Rows)
                    {
                        ChatlogItem item=new ChatlogItem();
                        item.id = row[0].ToString();
                        item.fromName = row[2].ToString();
                        item.fromID = row[1].ToString();
                        item.toName = row[4].ToString();
                        item.toID = row[3].ToString();
                        item.text= row[5].ToString();
                        String time = row[6].ToString();
                        item.date = DateTime.Parse(time);
                        if (item.fromID.Equals(fId))
                        {
                            item.subject = "Fethion-Chatlog: with " + item.toName;
                            item.content = time + ", " + item.fromName + "( " + item.fromID + " ): " + item.text;
                        }
                        else if (item.toID.Equals(fId))
                        {
                            item.subject = "Fethion-Chatlog: with " + item.fromName;
                            item.content = time + ", " + item.fromName + "( " + item.fromID + " ): " + item.text;
                        }
                        else
                        {
                            item.subject = "Fethion-Chatlog: " + item.fromName + " and " + item.toName;
                            item.content = time + ", From " + item.fromName + "( " + item.fromID + " ) , To " + item.toName + "( " + item.toID + " ): " + item.text;
                        }
//                        Console.WriteLine(string.Format("ID:{0} From:{1}({2}) To:{3}({4}) content:{5} @{6}",
//                            id, from, fromID, to, toID, content, time));
                        chatlogs[j++] = item;
                    }
                    parentUI.AddLog("Appending msg");
                    if (SaveRecord(chatlogs,fId))
                    {
                        for (int k = 0;k < chatlogs.Length;k++)
                        {
                            String cmdUpdate = "UPDATE MessageBody SET record=0 WHERE ID='" + chatlogs[k].id + "'";
                            myCommand = new SQLiteCommand(cmdUpdate, cnn);
                            ret = myCommand.ExecuteNonQuery();
                        }
                        parentUI.AddLog("Messages appended successed");
                    }
                    else
                    {
                        Console.WriteLine("Message appending failed");
                        parentUI.AddLog("Append failed");
                    }
                }
                cnn.Close();
                parentUI.AddLog("All message append success");
            }
            catch (Exception e)
            {
                parentUI.AddLog("Caught exception: " + e.Message);
                parentUI.SetControlPropertyValue(1);
            }
            finally
            {
                if (cnn != null)
                {
                    parentUI.SetControlPropertyValue(100);
                    client.LogOut();
                    cnn.Close();
                }
            }
        }
    }
}
